# -*- coding: UTF-8 -*-

import math
import time
import xlwt
import xlrd
from pathlib import Path
from xlutils.filter import process, XLRDReader, XLWTWriter


out_name = 'out.xls'
new_out_name = 'new.xls'
row = [u"序号", u"商品名称", u"商品条码", u"单位\n(盒)"]
chg = [u"号\n库存", u"号销售量"]
DAY = 24 * 60 * 60
style_list = []


# 通过时间戳获取当前号数
def get_date(tik):
    date_arr = time.localtime(tik)
    return [str(date_arr.tm_year), "{:02d}".format(date_arr.tm_mon), "{:02d}".format(date_arr.tm_mday)]


# 获取(r,c)单元格样式
def get_style(sheet, r, c):
    return style_list[sheet.cell_xf_index(r, c)]


# 写入Excel 先行后列，一横行，一竖列
def write_excel(tik, name, new_name=new_out_name):
    if Path(name).exists():
        print('编辑表格')
        # 获取原表格样式并保存到全局
        wb_origin = xlrd.open_workbook(name, formatting_info=True)
        wb_copy = XLWTWriter()
        process(XLRDReader(wb_origin, new_name), wb_copy)
        wb_edit = wb_copy.output[0][1]
        global style_list
        style_list = wb_copy.style_list
        date_now = get_date(tik)
        title = "{0} 年 {1} 月香烟销售记录表".format(date_now[0], date_now[1])
        # 遍历原表格文件
        for n, sheet in enumerate(wb_origin.sheets()):
            sh1 = wb_edit.get_sheet(n)
            if n == 0:
                sh1.write(0, 0, title, get_style(sheet, 0, 0))
                # 修改表头 只修改动态数组
                sh1.write(1, 4, get_date(tik)[2] + chg[0], get_style(sheet, 1, 4))
                for i in range(1, 9):
                    j = i % 2
                    tmp = tik + math.ceil(i / 2.0) * DAY
                    sh1.write(1, i + 4, get_date(tmp)[2] + chg[j], get_style(sheet, 1, i + 4))
        wb_edit.save(new_name)
    else:
        print('创建表格')
        wb = xlwt.Workbook()
        sh1 = wb.add_sheet(u'sheet1', cell_overwrite_ok=True)
        date_now = get_date(tik)
        sh1.write_merge(0, 0, 0, 12, "{0} 年 {1} 月香烟销售记录表".format(date_now[0], date_now[1]))
        # 添加静态表头
        for i in range(len(row)):
            sh1.write(1, i, row[i])
        # 添加动态日期相关表头
        sh1.write(1, 4, date_now[2] + chg[0])
        for i in range(1, 9):
            j = i % 2
            tmp = tik + math.ceil(i / 2.0) * DAY
            sh1.write(1, i + 4, get_date(tmp)[2] + chg[j])
        # 添加序号
        for i in range(2, 42):
            sh1.write(i, 0, i - 1)
        wb.save(name)
    print('保存完成～')


if __name__ == '__main__':
    date_input = input('输入年-月-日：')
    time_arr = None
    if date_input == '':
        tik_now = int(time.time())
        time_arr = time.localtime(tik_now)
    else:
        time_arr = time.strptime(date_input, "%Y-%m-%d")
        tik_now = int(time.mktime(time_arr))
    dt = get_date(tik_now)
    print("开始日期：{0}-{1}-{2}".format(dt[0], dt[1], dt[2]))
    dt = get_date(tik_now + 4 * DAY)
    print("截止日期：{0}-{1}-{2}".format(dt[0], dt[1], dt[2]))
    write_excel(tik_now, out_name, new_out_name)
